import numpy as np
import pandas as pd

# 该文件是在调整每列数据的方差,如果方差小于 0.00001, 就为数据添加噪音值调整, 貌似已经没有用了, 原因是生成每列数据的时候已经检查标准差是否接近0

def adjust_zero_variance_columns(dataframe, epsilon=1e-5):
    """
    Adjust columns with zero variance by adding small random noise.

    Parameters:
        dataframe (pd.DataFrame): Input DataFrame with columns to be adjusted.
        epsilon (float): A small value to add to the column to avoid zero variance.

    Returns:
        pd.DataFrame: DataFrame with adjusted columns.
    """
    adjusted_dataframe = dataframe.copy()

    for column in adjusted_dataframe.columns:
        variance = adjusted_dataframe[column].var()
        if variance < epsilon:
            # Add small random noise to the column
            noise = np.random.normal(scale=epsilon, size=len(adjusted_dataframe))
            adjusted_dataframe[column] += noise
            print(column,' 已被调整')

    return adjusted_dataframe

# 1e-5: 1除以10为5的幂，或0.00001。
def adjust_zero_variance_columnsV2(dataframe, epsilon=1e-5):
    """
    Adjust columns with zero variance by redistributing the sum of values.

    Parameters:
        dataframe (pd.DataFrame): Input DataFrame with columns to be adjusted.
        epsilon (float): A small value to add to the column to avoid zero variance.

    Returns:
        pd.DataFrame: DataFrame with adjusted columns.
    """
    adjusted_dataframe = dataframe.copy()

    for column in adjusted_dataframe.columns:
        variance = adjusted_dataframe[column].var()
        if variance < epsilon:
            # Find the column with the maximum value
            max_col = adjusted_dataframe[column].idxmax()
            
            # Find the column with the minimum value
            min_col = adjusted_dataframe[column].idxmin()
            
            # Redistribute the sum by moving 1 from max_col to min_col
            adjusted_dataframe.loc[max_col] -= 1
            adjusted_dataframe.loc[min_col] += 1

    return adjusted_dataframe

# 读取数据
sheet_name = 'sheet3Filled'
fileName = 'dest/20231130-160443_truncatedNew.xlsx'
dataset = pd.read_excel(fileName, sheet_name=sheet_name)

print('每列的方差: ',dataset.var())

# 调整数据
adjusted_dataset = adjust_zero_variance_columnsV2(dataset)

# 重新计算方差
variance_per_column = adjusted_dataset.var()
print('每列的方差 (调整后):', variance_per_column)


# 保存调整后的数据到Excel文件
""" with pd.ExcelWriter(fileName, mode='a', engine='openpyxl') as writer:
    adjusted_dataset.to_excel(writer, sheet_name='Sheet3', index=False) """